﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;
using Public.Logger;
using Public.Tools;

namespace ChoJu.Single.DAL
{
    public class SqliteDal
    {
        private static readonly object _obj = new object();
        private static SqliteDal _instance;
        public static SqliteDal Instance
        {
            get
            {
                if (_instance == null)
                    lock (_obj)
                        if (_instance == null)
                            _instance = new SqliteDal();

                return _instance;
            }
        }

        private SQLiteConnection _connection;
        private string _databaseString;
        private bool _isOpen;

        public SqliteDal()
        {
            
        }

        public bool Init()
        {
            try
            {
                var dir = Path.Combine(AppContext.BaseDirectory, "Data");
                if(Directory.Exists(dir) == false)
                    Directory.CreateDirectory(dir);
                
                _databaseString = Path.Combine(AppContext.BaseDirectory, "Data", "choju_single_sqlite.db");
                if (File.Exists(_databaseString) == false)
                    SQLiteConnection.CreateFile(_databaseString);

                _connection = new SQLiteConnection($"Data Source={_databaseString};Version=3;");
                Open();
                VacuumDatabase();

                var tabName = $"tab_info";
                if(TableExists(tabName) == false)
                {
                    var init = GetInitString(tabName);
                    ExecuteNonQuery(init);
                }

                return true;
            }
            catch (Exception ex)
            {
                Logger.Error("初始化SQLite数据库异常", ex);
                return false;
            }
        }

        private bool Open(bool open = false)
        {
            try
            {
                if (_isOpen == true & open == false)
                    return true;

                _connection.Open();
                _isOpen = true;
                return true;
            }
            catch (Exception ex)
            {
                Logger.Error("打开数据库失败", ex);
                return false;
            }
        }

        public int ExecuteNonQuery(string sql)
        {
            try
            {
                lock (_obj)
                {
                    Open();

                    SQLiteCommand command = new SQLiteCommand(sql, _connection);
                    return command.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                Logger.Error("执行SQL语句异常 SQL:" + sql, ex);
                return -1;
            }
        }

        public object Execute(string sql)
        {
            try
            {
                Open();

                var obj = SQLiteCommand.Execute(sql, SQLiteExecuteType.Default, _databaseString);
                return obj;
            }
            catch (Exception ex)
            {
                Logger.Error("执行SQL语句异常 SQL:" + sql, ex);
                return -1;
            }
        }

        public int Delete(string delete)
        {
            try
            {
                Open();

                SQLiteCommand command = new SQLiteCommand(delete, _connection);
                return command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Logger.Error("删除数据异常 SQL:" + delete, ex);
                return -1;
            }
        }

        public int Insert<T>(T t, string tabName, List<string> filter = null)
        {
            string sql = string.Empty;
            try
            {
                Open();

                sql = InsertSQL<T>(t, tabName);
                SQLiteCommand command = new SQLiteCommand(sql, _connection);
                return command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Logger.Error("插入数据异常 SQL:" + sql, ex);
                return -1;
            }
        }

        public DataTable Search(string sql)
        {
            try
            {
                Open();

                SQLiteCommand command = new SQLiteCommand(sql, _connection);
                return ExecuteDatatable(command);
            }
            catch (Exception ex)
            {
                Logger.Error("查询数据异常 SQL:" + sql, ex);
                return null;
            }
        }

        public List<T> Search<T>(string sql) where T : new()
        {
            try
            {
                lock (_obj)
                {
                    Open();

                    SQLiteCommand command = new SQLiteCommand(sql, _connection);
                    var dt = ExecuteDatatable(command);
                    return Tool.DataTableToList<T>(dt);
                }
            }
            catch (Exception ex)
            {
                Logger.Error("查询数据异常 SQL:" + sql, ex);
                return null;
            }
        }

        public int Update(string update)
        {
            try
            {
                Open();

                SQLiteCommand command = new SQLiteCommand(update, _connection);
                return command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Logger.Error("更新数据异常 SQL:" + update, ex);
                return -1;
            }
        }

        public bool TableExists(string tableName)
        {
            using (var command = new SQLiteCommand(_connection))
            {
                command.CommandText = "SELECT name FROM sqlite_master WHERE type='table' AND name=@name";
                command.Parameters.AddWithValue("@name", tableName);

                var result = command.ExecuteScalar();
                return result != null && result.ToString() == tableName;
            }
        }

        private string GetInitString(string tabName)
        {
            return $"CREATE TABLE  {tabName}  ( LevelCode  varchar(10), Count  int, IsUpload  int, CreateTime DATE );";
        }

        public bool DropTable(string tabName)
        {
            try
            {
                var sql = $"DROP TABLE IF EXISTS  {tabName} ";
                var res = ExecuteNonQuery(sql);
                return true;
            }
            catch(Exception ex)
            {
                return false;
            }
        }

        public string InsertSQL<T>(T t, string tabName)
        {
            var props = typeof(T).GetProperties();

            StringBuilder sbCol = new StringBuilder();
            StringBuilder sbVal = new StringBuilder();

            foreach (PropertyInfo pi in props)
            {
                var name = pi.Name;
                var ty = pi.PropertyType;
                var val = pi.GetValue(t, null);
                sbCol.Append($"`{name}`").Append(",");
                sbVal.Append(GetValue(val, ty)).Append(",");
            }

            return $"insert into {tabName} " +
                $"({sbCol.ToString().TrimEnd(',')}) " +
                $"values " +
                $"({sbVal.ToString().TrimEnd(',')})";
        }

        private string GetValue(object val, Type ty)
        {
            var value = val?.ToString();
            var type = ty.ToString().ToLower().Trim().TrimEnd(']');

            if (string.IsNullOrEmpty(value) == true)
                return "''";

            if (type.EndsWith("int32") || type.EndsWith("long") ||
                type.EndsWith("float") || type.EndsWith("double") || type.EndsWith("decimal"))
                return value;
            else if (type.EndsWith("boolean"))
                return value.ToLower() == "true" ? "1" : "0";
            else if (ty.IsEnum == true)
                return ((int)val).ToString();
            else if (type.EndsWith("datetime"))
                return $"'{((DateTime)val).ToString("yyyy-MM-dd HH:mm:ss")}'";
            else
                return $"'{value}'";
        }

        public DataTable ExecuteDatatable(SQLiteCommand cmd)
        {
            if (cmd.Connection.State == ConnectionState.Closed)
                cmd.Connection.Open();
            DataTable dt = new DataTable();
            SQLiteDataAdapter da = new SQLiteDataAdapter(cmd);
            da.Fill(dt);
            da.Dispose();
            cmd.Dispose();
            return dt;
        }


        private void VacuumDatabase()
        {
            using (var command = _connection.CreateCommand())
            {
                command.CommandText = "VACUUM";
                command.ExecuteNonQuery();
            }
        }
    }
}
